﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using CustomsSystem.Model;
using Maticsoft.DBUtility;

namespace CustomsSystem.DAL
{
    public class EmpService
    {
        /// <summary>
        #region /// 是否存在该记录
        /// </summary>
        public bool Exists(string EmpNum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Cu_User");
            strSql.Append(" where empnum='" + EmpNum + "'"); 
            return DbHelperSQL.Exists(strSql.ToString());
        }

        #endregion

        /// <summary>
        #region   /// 增加一条数据
        /// </summary>
        public bool Add(CustomsSystem.Model.Info_emp model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.EmpNum != null)
            {
                strSql1.Append("EmpNum,");
                strSql2.Append("'" + model.EmpNum + "',");
            }
            if (model.UserName != null)
            {
                strSql1.Append("UserName,");
                strSql2.Append("'" + model.UserName + "',");
            }
            if (model.PassWord != null)
            {
                strSql1.Append("PassWord,");
                strSql2.Append("'" + model.PassWord + "',");
            }

            if (model.JobNo != null)
            {
                strSql1.Append("JobNo,");
                strSql2.Append("'" + model.JobNo + "',");
            }

            if (model.Remarks != null)
            {
                strSql1.Append("Remarks,");
                strSql2.Append("'" + model.Remarks + "',");
            }



            strSql.Append("insert into Cu_User(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 更新一条数据
        public bool Update(CustomsSystem.Model.Info_emp model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Cu_User set ");
            if (model.EmpNum != null)
            {
                strSql.Append("EmpNum='" + model.EmpNum + "',");
            }
            else
            {
                strSql.Append("EmpNum= null ,");
            }

            if (model.UserName != null)
            {
                strSql.Append("UserName='" + model.UserName + "',");
            }
            else
            {
                strSql.Append("UserName= null ,");
            }

            if (model.PassWord != null)
            {
                strSql.Append("PassWord='" + model.PassWord + "',");
            }
            else
            {
                strSql.Append("PassWord= null ,");
            }

            if (model.JobNo != null)
            {
                strSql.Append("JobNo='" + model.JobNo + "',");
            }
            else
            {
                strSql.Append("JobNo=null,");
            }

            if (model.Remarks != null)
            {
                strSql.Append("Remarks ='" + model.Remarks + "',");
            }
            else
            {
                strSql.Append("Remarks =null,");
            }

            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where EmpNum='" + model.EmpNum + "' ");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 删除一条数据
        public bool Delete(string EmpNum)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("delete from Cu_User ");
            strSql.Append(" where EmpNum='" + EmpNum + "' ");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 得到一个实体对象
        public CustomsSystem.Model.Info_emp GetModel(string EmpNum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1  ");
            strSql.Append(" EmpNum,UserName,PassWord,Jobno,Remarks  ");
            strSql.Append(" from Cu_User ");
            strSql.Append(" where EmpNum='" + EmpNum + "' ");
            CustomsSystem.Model.Info_emp model = new CustomsSystem.Model.Info_emp();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.Info_emp DataRowToModel(DataRow row)
        {
            CustomsSystem.Model.Info_emp model = new CustomsSystem.Model.Info_emp();
            if (row != null)
            {

                if (row["EmpNum"] != null)
                {
                    model.EmpNum = row["EmpNum"].ToString();
                }
                if (row["UserName"] != null)
                {
                    model.UserName = row["UserName"].ToString();
                }
                if (row["PassWord"] != null)
                {
                    model.PassWord = row["PassWord"].ToString();
                }
                if (row["JobNo"] != null)
                {
                    model.JobNo = row["JobNo"].ToString();
                }
                if (row["Remarks"] != null)
                {
                    model.Remarks = row["Remarks"].ToString();
                }

            }
            return model;
        }
        #endregion


        #region  获得数据列表
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select empNum,UserName,PassWord,JobNo,Remarks ");
            strSql.Append(" FROM Cu_User ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion
    }
}
